Skip Headers

Oracle® Database SQLJ Developer's Guide and Reference 10g Release 2 (10.2) Part Number B16018-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 11 SQLJ in the Server

SQLJ applications can be stored and run directly in the Oracle Database 10g server. You have the option of either translating and compiling them on a client and loading the generated classes and resources into the server or loading SQLJ source code into the server and having it translated and compiled by the embedded translator of the server.

This chapter discusses features and usage of SQLJ in the server, including additional considerations, such as multithreading and recursive SQLJ calls.

The following topics are discussed:

- Introduction to Server-Side SQLJ

- Creating SQLJ Code for Use in the Server

- Translating SQLJ Source on a Client and Loading Components

- Loading SQLJ Source and Translating in the Server

- Dropping Java Schema Objects

- Additional Server-Side Considerations

## Introduction to Server-Side SQLJ

SQLJ code, as with any Java code, can run in Oracle Database 10g in stored procedures, stored functions, or triggers. Data access is through a server-side implementation of the SQLJ run time in combination with the Oracle Java Database Connectivity (JDBC) server-side internal driver. In addition, an embedded SQLJ translator in Oracle Database 10g is available to translate SQLJ source files directly in the server.

Considerations for running SQLJ in the server include several server-side coding issues as well as decisions about where to translate your code and how to load it into the server. You must also be aware of how the server determines the names of generated output. You can either translate and compile on a client and load the class and resource files into the server or you can load `.sqlj` source files into the server and have the files automatically translated by the embedded SQLJ translator.

The embedded translator has a different user interface than the client-side translator. Supported options can be specified using a database table, and error output is to a database table. Output files from the translator are transparent to the developer.

Notes: In Oracle Database 10g release 2 (10.2), the server uses a Java Development Kit (JDK) 1.5 Java2 Platform, Standard Edition (J2SE) environment. The server-side SQLJ environment is roughly equivalent to a client-side environment using the `runtime12ee` library, except for SQLJ-specific connection bean support and considering any relevant exceptions noted in "Creating SQLJ Code for Use in the Server". This manual presumes that system configuration issues are outside the duties of most SQLJ developers. Therefore, configuration of the Oracle Database 10g Java virtual machine (JVM) is not covered here. For information about setting Java-related configuration parameters, refer to the Oracle Database Java Developer's Guide. If you need information about configuring the multithreaded server, dispatcher, or listener, refer to the Oracle Database Net Services Administrator's Guide.

Note Regarding Desupport of J2EE in the Oracle Database

Since the introduction of Oracle9i Application Server Containers for J2EE (OC4J), a new, lighter-weight, easier-to-use, faster, and certified Java2 Platform, Enterprise Edition (J2EE) container, Oracle has desupported the J2EE and Common Object Request Broker Architecture (CORBA) stacks from the database. However, the database-embedded JVM (Oracle JVM) will still be present and will continue to be enhanced to offer J2SE features, Java stored procedures, JDBC, and SQLJ in the database.

To summarize, Oracle no longer supports the following technologies in the database:

- The J2EE stack, consisting of Enterprise Beans (EJB) container, JavaServer Pages (JSP) container, and Oracle9i Servlet Engine (OSE)

- The embedded CORBA framework, based on Visibroker for Java

Customers can no longer deploy servlets, JSP pages, EJBs, and CORBA objects in Oracle databases. Oracle9i Database release 1 (9.0.1) was the last database release to support the J2EE and CORBA stacks. Oracle encourages customers to migrate to OC4J for J2EE applications that previously ran in the database.

## Creating SQLJ Code for Use in the Server

With few exceptions, writing SQLJ code for use within the target Oracle Database 10g instance is identical to writing SQLJ code for client-side use. The few differences are due to Oracle JDBC characteristics or general Java characteristics in the server, rather than being specific to SQLJ. There are a few considerations to be aware of, however:

- There is an implicit connection to the server itself.

- There are coding issues, such as lack of auto-commit functionality.

- In the server, the default output device is the current trace file.

- Name resolution functions differently in the server than on a client.

- SQL names must be interpreted and processed differently from Java names.

- There is no JSP, EJB, or CORBA functionality in the server. Because there is no JSP container, you cannot use the SQLJ JSP connection beans in server-side code.

Note: Writing SQLJ code to connect from one server to another through the server-side Thin driver is identical to writing code for an application that uses a client-side JDBC Thin driver. The points in this discussion do not apply.

This section covers the following topics:

- Database Connections Within the Server

- Coding Issues Within the Server

- Default Output Device in the Server

- Name Resolution in the Server

- SQL Names Versus Java Names

### Database Connections Within the Server

The concept of connecting to a server is different when your SQLJ code is running within the server itself. There is no explicit database connection. By default, an implicit channel to the database is used for any Java program running in the server. You do not have to initialize this connection, as it is automatically initialized for SQLJ programs. You do not have to register or specify a driver, create a connection instance, specify a default connection context, specify any connection objects for any of your `#sql` statements, or close the connection.

Note: In the server, setting the default connection context to `null`, as follows, will reinstall the default connection context (the implicit connection to the server):
    DefaultContext.setDefaultContext(null);

### Coding Issues Within the Server

There are a few coding issues to consider when your code will run within the target server using the server-side internal driver. Note the following:

- Result sets issued by the internal driver persist across calls, and their finalizers do not release their cursors. Because of this, it is especially important to close all iterators to avoid running out of available cursors, unless you have a particular reason for keeping an iterator open, such as when it is actually used across calls.

- The internal driver does not support auto-commit functionality, so the auto-commit setting is ignored within the server. Use explicit `COMMIT` or `ROLLBACK` statements to implement or cancel your data updates:

      #sql { COMMIT };
      ...
      #sql { ROLLBACK };

Note: If you are using any kind of XA transactions, such as Java Transaction Service (JTS) transactions, you cannot use SQLJ or JDBC `COMMIT`/`ROLLBACK` statements or methods.

- For ISO standard code generation, if you use SQLJ code that interacts with JDBC code and you use a nondefault connection context instance, then you must eventually close the connection context instance in order to clean up statements cached there, unless you use the same connection context instance for the duration of your session. Following is an example:

      DefaultContext ctx = new DefaultContext(conn); // conn is JDBC connection
      #sql [ctx] { SQL operation };
      ...
      ctx.close(sqlj.runtime.ConnectionContext.KEEP_CONNECTION);
      ...

  If you do not close the connection context instance, you are likely to run out of statement handles in your session. Also be aware that simply closing the underlying JDBC connection object does not reclaim statement handles, which differs from the behavior when the application executes on a client.

  For the default Oracle-specific code generation, statements are cached in the underlying JDBC statement cache and can be automatically reclaimed.

- With Oracle-specific code generation for code that will run in the server, use an explicit `ExecutionContext` instance. This ensures that your application can fully interoperate with applications translated with ISO standard SQLJ code generation.

  If you use one thread per connection, which translates to one thread per Oracle session, it is sufficient to use one static instance, as in the following example:

      public static ExecutionContext ec = new ExecutionContext();
      ...
      #sql [ec] { SQL operation };    // use ec for all operations

  If you use multiple threads per connection, then you must use a separate execution context instance for each method invocation.

See Also: Oracle Database JDBC Developer's Guide and Reference

### Default Output Device in the Server

The default standard output device in the Oracle Java virtual machine (JVM) is the current trace file. If you want to reroute all standard output from a program executing in the server, for example, output from any `System.out.println()` calls, to a user screen, then you can execute the `SET_OUTPUT()` procedure of the `DBMS_JAVA` package as in the following example. Input the buffer size in bytes (10,000 bytes in this case).

    sqlplus> execute dbms_java.set_output(10000);

Output exceeding the buffer size will be lost.

If you want your code executing in the server to expressly write output to the user screen, then you can also use the PL/SQL `DBMS_OUTPUT.PUT_LINE()` procedure instead of the Java `System.out.println()` method. The `PUT_LINE()` procedure is overloaded, accepting either `VARCHAR2`, `NUMBER`, or `DATE` as input to specify what is printed.

See Also: Oracle Database PL/SQL Packages and Types Reference

### Nam e Resolution in the Server

Class loading and name resolution in the server follow a very different paradigm than on a client, because the environments themselves are very different.

Java name resolution in the Oracle JVM includes the following:

- Class resolver specs, which are schema lists to search in resolving a class schema object (functionally equivalent to the classpath on a client)

- The resolver, which maintains mappings between class schema objects that reference each other in the server

A class schema object must be resolved before Java objects of the class can be instantiated or methods of the class can be executed. A class schema object is said to be resolved when all of its external references to Java names are bound. In general, all the classes of a Java program should be compiled or loaded before they can be resolved. This is because Java programs are typically written in multiple source files that can reference each other recursively.

When all the class schema objects of a Java program in the server are resolved and none of them have been modified since being resolved, the program is effectively prelinked and ready to run.

Note: The `loadjava` utility resolves references to classes but not to resources. For ISO standard code, which has to be translated on the client, be careful how you load any resources into resource schema objects in the server. If you enabled the SQLJ `-ser2class` flag for your client-side translation, then your SQLJ profiles will be in class files and you will typically not have any resource files. If you did not enable `-ser2class`, then your profiles will be in `.ser` resource files.

See Also: Oracle Database Java Developer's Guide

### SQL Names Versus Java Names

SQL names, such as names of source, class, and resource schema objects, are not global in the way that Java names are global. The Java Language Specification (JLS) directs that package names use Internet naming conventions to create globally unique names for Java programs. By contrast, a fully qualified SQL name is interpreted only with respect to the current schema and database. For example, the `SCOTT.FIZZ` name in one database does not necessarily denote the same program as `SCOTT.FIZZ` in another database. In fact, `SCOTT.FIZZ` in one database can even call `SCOTT.FIZZ` in another database.

Because of this inherent difference, SQL names must be interpreted and processed differently than Java names. SQL names are relative names and are interpreted from the point of view of the schema where a program is executed. This is central to how the program binds local data stored at that schema. Java names are global names, and the classes that they designate can be loaded at any execution site, with reasonable expectation that those classes will be classes that were used to compile the program.

## Translating SQLJ Source on a Client and Loading Components

One approach to deploying SQLJ code in Oracle Database 10g is to run the SQLJ translator on a client computer to take care of translation, compilation, and profile customization, if applicable. Then load the resulting class and resource files, if any, into the server, typically using a Java Archive (JAR) file. In fact, this is the only way to use ISO standard code in the server, because the server-side translator supports only Oracle-specific code generation.

If you are developing your source on a client computer, as is usually the case, and have a SQLJ translator available there, then this approach is advisable. It provides flexibility in running the translator, because option-setting and error-processing are not as convenient in the server.

For ISO standard code, it might also be advisable to use the SQLJ `-ser2class` option during translation when you intend to load an application into the server. This results in SQLJ profiles being converted from `.ser` serialized resource files to `.class` files and simplifies their naming. However, be aware that profiles converted to `.class` files cannot be further customized. To further customize, you would have to rerun the translator and regenerate the profiles.

See Also: "Conversion of .ser File to .class File (-ser2class)"

When you load `.class` files and `.ser` resource files into Oracle Database 10g, either directly or using a JAR file, the resulting library units are referred to as Java class schema objects and Java resource schema objects. Your SQLJ profiles, if any, will be in resource schema objects, if you load them as `.ser` files, or in class schema objects if you enabled `-ser2class` during translation and load them as `.class` files.

This section covers the following topics:

- Loading Classes and Resources into the Server

- Naming of Loaded Class and Resource Schema Objects

- Publishing the Application After Loading Class and Resource Files

- Summary: Running a Client Application in the Server

### Loading Classes and Resources into the Server

Once you run the translator on the client, use the Oracle `loadjava` client-side utility to load class and resource files into schema objects in the server. Either specify the class and resource files, if any, individually on the `loadjava` command line, or put them into a JAR file and specify the JAR file on the command line. A separate schema object is created for each `.class` or `.ser` file in the JAR file or on the command line.

Consider an example where you do the following:

1. Translate and compile `Foo.sqlj`, which includes an iterator declaration for `MyIter`, using ISO standard code generation.

2. Enable the `-ser2class` option when you translate `Foo.sqlj`.

3. Archive the resulting files, `Foo.class`, `MyIter.class`, `Foo_SJProfileKeys.class`, and `Foo_SJProfile0.class`, into `Foo.jar`.

Then run `loadjava` with the following command line (plus any options you want to specify). This examples uses the default JDBC Oracle Call Interface (OCI) driver:

    % loadjava -user scott/tiger Foo.jar

Alternatively, you can use the original files:

    % loadjava -user scott/tiger Foo.class MyIter.class Foo_SJProfileKeys.class Foo_SJProfile0.class

or:

    % loadjava -user scott/tiger Foo*.class MyIter.class

You can use the JDBC Thin driver for loading as follows (specifying the `-thin` option and an appropriate URL):

    % loadjava -thin -user scott/tiger@localhost:1521/myservice Foo.jar

See Also: "Code Generation" and "Java Compilation"

Notes: When you use the `-codegen=iso` setting during translation, generating profile files and then loading the profiles into the server as `.ser` files, they are first customized if they were not already customized on the client. If they were already customized, then they are loaded as is. You can access the `USER_OBJECTS` view in your schema to verify that your classes and resources are loaded properly.

Although the `loadjava` utility is recommended for loading your SQLJ and Java applications into the server, you can also use SQL `CREATE JAVA` commands such as the following:

    CREATE OR REPLACE <AND RESOLVE> JAVA CLASS <NAMED name>;

    CREATE OR REPLACE JAVA RESOURCE <NAMED name>;

See Also: Oracle Database SQL Reference and Oracle Database Java Developer's Guide

### Naming of Loaded Class and Resource Schema Objects

This section discusses how schema objects for classes and profiles are named when you load classes and profiles into the server. However, remember that profiles are created only for ISO standard code generation.

For ISO standard code generation, if the SQLJ `-ser2class` option was enabled when you translated your application on the client, then profiles were converted to `.class` files and will be loaded into class schema objects in the server. If `-ser2class` was not enabled, then profiles were generated as `.ser` serialized resource files and will be loaded into resource schema objects in the server.

In the following discussion, it is assumed that you use only the default connection context class for any application that will run in the server. Therefore, there will be only one profile.

Full N ames and Short Names

There are two forms of schema object names in the server, full names and short names. Full names are fully qualified and are used as the schema object names whenever possible. If any full name is longer than 31 characters, however, or contains characters that are illegal or cannot be converted to characters in the database character set, then Oracle Database 10g converts the full name to a short name to use as the name of the schema object, keeping track of both names and how to convert between them. If the full name is 31 characters or less and has no illegal or inconvertible characters, then the full name is used as the schema object name.

For more information about these and about other file naming considerations, including `DBMS_JAVA` procedures to retrieve a full name from a short name, and vice versa, refer to Oracle Database Java Developer's Guide

Full Names of Loaded Classes

Loaded classes will include profile files if you use ISO standard code generation and enable the `-ser2class` flag. The full name of the class schema object produced when you load a `.class` file into the server is determined by the package and class name in the original source code. Any path information you supply on the command line or in the JAR file is irrelevant in determining the name of the schema object. For example, if `Foo.class` consists of the `Foo` class, which was specified in the source code as being in the `x.y` package, then the full name of the resulting class schema object is as follows:

    x/y/Foo

Note that the `.class` extension is dropped.

If `Foo.sqlj` declares an iterator, `MyIter`, then the full name of its class schema object is as follows (unless it is a nested class, in which case it will not have its own schema object):

    x/y/MyIter

Furthermore, if you are using ISO standard code generation:

- The related profile-keys class file, generated by SQLJ when you translate `Foo.sqlj`, is `Foo_SJProfileKeys.class`. Therefore, the full name of its class schema object is:

      x/y/Foo_SJProfileKeys

- If the `-ser2class` option was enabled when you translated your application, then the resulting profile is generated in `Foo_SJProfile0.class`. Therefore, the full name of the class schema object is:

      x/y/Foo_SJProfile0

Full Names of Loaded Resources

This discussion is relevant only if you are using ISO standard code generation and did not enable the `-ser2class` option when you translated your application, or if you use other Java serialized resource (`.ser`) files in your application.

The naming of resource schema objects is handled differently from class schema objects. Their names are not determined from the contents of the resources. Instead, their full names are identical to the names that appear in a JAR file or on the `loadjava` command line, including path information. Also note that the `.ser` extension is not dropped.

It is important to note that because resource names are used to locate the resources at run time. Their names must include the correct path information. In the server, the correct full name of a resource is identical to the relative path and file name that Java would use to look it up on a client.

In the case of a SQLJ profile, this is a subdirectory under the directory specified by the translator `-d` option, according to the package name. If the `-d` option, used to specify the top-level output directory for generated `.class` and `.ser` files, is set to `/mydir` and the application is in the `abc.def` package, then `.class` and `.ser` files generated during translation will be placed in the `/mydir/abc/def` directory.

See Also: "Output Directory for Generated .ser and .class Files (-d)"

At run time, `/mydir` would presumably be in your classpath and Java will look for your application components in the `abc/def` directory underneath it. Therefore, when you load this application into the server, you must run `loadjava` or `jar` from the `-d` directory so that the path you specify on the command line to find the files also indicates the package name, as follows (where `%` is the system prompt):

    % cd /mydir
    % loadjava <...options...> abc/def/*.class abc/def/*.ser

Alternatively, to use a JAR file:

    % cd /mydir
    % jar -cvf myjar.jar abc/def/*.class abc/def/*.ser
    % loadjava <...options...> myjar.jar

If your application is `App` and your profile is `App_SJProfile0.ser`, then either of the preceding examples will correctly result in the following full name of the created resource schema object:

    abc/def/App_SJProfile0.ser

Note that `.ser` is retained.

Note also that if you set `-d` to a directory whose hierarchy has no other contents (which is advisable), you can simply run the JAR utility as follows to recursively get your application components:

    % cd /mydir
    % jar -cvf myjar.jar *
    % loadjava <...options...> myjar.jar

### Publishing the Application After Loading Class and Resource Files

Before using your SQLJ code in the server, you must publish the top-level methods, as is true of any Java code you use in the server. Publishing includes writing call descriptors, mapping data types, and setting parameter modes.

See Also: Oracle Database Java Developer's Guide

### Summary: Running a Client Application in the Server

This section summarizes the typical steps of running a client application in the server. As an example, it uses a demo application called `NamedIterDemo`.

1. Create a JAR file for your application components. For `NamedIterDemo`, the components include `SalesRec.class` as well as the application class and profile, if any.

   You can create JAR file `niter-server.jar` as follows:

       % jar cvf niter-server.jar Named*.class Named*.ser SalesRec.class connect.properties

   But remember that `.ser` files are only relevant for ISO standard code generation.

2. Load the JAR file into the server.

   Use `loadjava` as follows. This example instructs `loadjava` to use the OCI driver in loading the files. The `-resolve` option results in the class files being resolved.

       % loadjava -oci -resolve -force -user scott/tiger niter-server.jar

3. Create a SQL wrapper in the server for your application.

   For example, run a SQL*Plus script that executes the following:

       set echo on
       set serveroutput on
       set termout on
       set flush on

       execute dbms_java.set_output(10000);

       create or replace procedure SQLJ_NAMED_ITER_DEMO as language java
       name 'NamedIterDemo.main (java.lang.String[])';
       /

   The `DBMS_JAVA.SET_OUTPUT()` routine reroutes default output to your screen, instead of to a trace file. The input parameter is the buffer size in bytes.

4. Execute the wrapper.

   For example:

       sqlplus> call SQLJ_NAMED_ITER_DEMO();

## Loading SQLJ Source and Translating in the Server

Another approach to developing SQLJ code for the server is loading the source code into the server and translating it directly in the server. This uses the embedded SQLJ translator in the Oracle JVM. This discussion still assumes you created the source on a client computer.

Note: The server-side SQLJ translator does not support ISO standard code generation. If you want to use such code in the server, you must translate on a client and load the individual class files and resources into the server.

As a general rule, loading SQLJ source into the server is identical to loading Java source into the server, with translation taking place implicitly when a compilation option is set, such as the `loadjava -resolve` option. When you load `.sqlj` source files into Oracle Database 10g, either directly or using a JAR file, the resulting library units containing the source code are referred to as Java source schema objects. A separate schema object is created for each source file.

When translation and compilation take place, the resulting library units for the generated classes are referred to as Java class schema objects, just as they are when loaded directly into the server from `.class` files created on a client. A separate schema object is created for each class. Resource schema objects are used for properties files that you load into the server.

This section covers the following topics:

- Loading SQLJ Source Code into the Server

- Option Support in the Server Embedded Translator

- Naming of Loaded Source and Generated Class and Resource Schema Objects

- Error Output from the Server Embedded Translator

- Publishing the Application After Loading Source Files

See Also: Oracle Database SQL Reference and Oracle Database Java Developer's Guide

### Loading SQLJ Source Code into the Server

Use the Oracle `loadjava` client-side utility on a `.sqlj` file, instead of on a `.class` file, to load source into the server. If you enable the `loadjava -resolve` option in loading a `.sqlj` file, then the server-side embedded translator is run to perform the translation and compilation of your application as it is loaded. Otherwise, the source is loaded into a source schema object without any translation. However, in this case, the source is implicitly translated and compiled the first time an attempt is made to use a class defined in the source. Such implicit translation might seem surprising at first, because there is nothing comparable in client-side SQLJ.

For example, run `loadjava` as follows from the system prompt:

    % loadjava -user scott/tiger -resolve Foo.sqlj

Alternatively, you can use the JDBC Thin driver to load:

    % loadjava -thin -user scott/tiger@localhost:1521/myservice -resolve Foo.sqlj

Either of these will result in appropriate class schema objects being created in addition to the source schema object.

Before running `loadjava`, however, you must set SQLJ options appropriately. Note that encoding can be set on the `loadjava` command line, instead of through the server-side SQLJ `encoding` option, as follows:

    % loadjava -user scott/tiger -resolve -encoding SJIS Foo.sqlj

The `loadjava` script, which runs the actual utility, is in the `bin` subdirectory under your `ORACLE_HOME` directory. This directory should already be in your path once Oracle has been installed.

Notes: In processing a JAR file, `loadjava` first processes `.sqlj`, `.java`, and `.class` files. It then makes a second pass and processes everything else as Java resource files. You cannot load a `.sqlj` file along with `.class` files that were generated from processing of the same `.sqlj` file. This would create an obvious conflict, because the server would be trying to load the same classes that it would also be trying to generate. You can put multiple `.sqlj` files into a JAR file and specify the JAR file to `loadjava`. You can access the `USER_OBJECTS` view in your schema to verify that your classes are loaded properly.

Although the `loadjava` utility is recommended for loading your SQLJ and Java applications into the server, you can also use SQL `CREATE JAVA` commands such as the following:

    CREATE OR REPLACE <AND COMPILE> JAVA SOURCE <NAMED srcname> <AS loadname>;

If you specify `AND COMPILE` for a `.sqlj` file, then the source is translated and compiled at that time, creating class schema objects as appropriate in addition to the source schema object. Otherwise, it is not translated and compiled. In this case, only the source schema object is created. In this latter case, however, the source is implicitly translated and compiled the first time an attempt is made to use a class contained in the source.

Note: When you first load a source file, some checking of the source code is performed, such as determining what classes are defined. If any errors are detected at this time, the load fails.

### Option Support in the Server Embedded Translator

The following options are available in the server-side SQLJ translator:

- `encoding`

- `online`

- `debug`

This section discusses these options, after leading off with some discussion of fixed settings in server-side SQLJ. There is also discussion of the `loadjava` utility and its `-resolve` option.

Fixe d Settings in the Server-Side SQLJ Translator

The following settings, supported by SQLJ translator options on a client, are fixed in the server-side translator:

- Both online semantics-checking and offline parsing are enabled in the server by default, equivalent to the default `-parse=both` setting on a client. You can override this to disable online semantics-checking through the `online` option, but cannot disable offline parsing.

  See Also: "Online Semantics-Checking Versus Offline Parsing"

- Oracle-specific code generation is used in the server, equivalent to the default `-codegen=oracle` setting on a client. This is a fixed setting.

  See Also: "Oracle-Specific Code Generation (No Profiles)"

- Class schema objects created during server-side translation reference line numbers that map to the SQLJ source code. This is equivalent to enabling the `-linemap` option when you translate on a client.

  See Also: "Line-Mapping to SQLJ Source File (-linemap)"

The encoding Option

This option determines any encoding used to interpret your source code when it is loaded into the server. The `encoding` option is used at the time the source is loaded, regardless of whether it is also compiled. Alternatively, when using `loadjava` to load your SQLJ application into the server, you can specify encoding on the `loadjava` command line. Any `loadjava` command-line setting for encoding overrides this `encoding` option.

Note: If no encoding is specified, either through this option or through `loadjava`, then encoding is performed according to the `file.encoding` setting of the client from which you run `loadjava`.

See Also: "Encoding for Input and Output Source Files (-encoding)"

The online Option

A `true` setting for the `online` option (the default value) enables online semantics-checking. Semantics-checking is performed relative to the schema in which the source is loaded. You do not specify an exemplar schema, as you do for online-checking on a client. If the `online` option is set to `false`, offline checking is performed.

In either case, the default checker is `oracle.sqlj.checker.OracleChecker`, which will choose an appropriate checker according to your JDBC driver version and Oracle version.

See Also: "Semantics-Checkers and the OracleChecker Front End (default checker)"

The `online` option is used at the time the source is translated and compiled. If you load it with the `loadjava` `-resolve` option enabled, then this will occur immediately. Otherwise it will occur the first time an attempt is made to use a class defined in the source, resulting in implicit translation and compilation.

Note: The `online` option is used differently in the server than on a client. In the server, the `online` option is only a flag that enables online checking using a default checker. On a client, the `-online` option specifies which checker to use, but it is the `-user` option that enables online checking.

The debug Option

Setting this option to `true` instructs the server-side Java compiler to output debugging information when a `.sqlj` or `.java` source file is compiled in the server. This is equivalent to using the `-g` option when running the standard `javac` compiler on a client.

Source is compiled during loading if you use the `loadjava` `-resolve` option, right after SQLJ translation in the case of a `.sqlj` file. If you do not use the `-resolve` option, then implicit translation and compilation occurs the first time an attempt is made to use a class defined in the source.

Setting SQLJ Options in the Server

There is no command line and there are no properties files when running the SQLJ translator in the server. Information about translator and compiler options is held in each schema in a table named `JAVA$OPTIONS`. Manipulate options in this table through the following functions and procedures of the `DBMS_JAVA` package:

- `DBMS_JAVA.GET_COMPILER_OPTION()`

- `DBMS_JAVA.SET_COMPILER_OPTION()`

- `DBMS_JAVA.RESET_COMPILER_OPTION()`

Use `set_compiler_option()` to specify separate option settings for individual packages or sources. It takes the following as input, with each parameter enclosed by single-quotes:

- Package name, using dotted names, or source name

  Specify this as a full name, not a short name. If you specify a package name, then the option setting applies to all sources in that package and subpackages, except where you override the setting for a particular subpackage or source.

- Option name

- Option setting

Execute the `DBMS_JAVA` routines using SQL*Plus as follows:

    sqlplus> execute dbms_java.set_compiler_option('x.y', 'online', 'true');
    sqlplus> execute dbms_java.set_compiler_option('x.y.Create', 'online', 'false');

These two commands enable online checking for all sources in the `x.y` package, then override that for the `Create` source by disabling online checking for that particular source.

Similarly, set encoding for the `x.y` package to `SJIS` as follows:

    sqlplus> execute dbms_java.set_compiler_option('x.y', 'encoding', 'SJIS');

Server-Side Option Notes

Be aware of the following:

- The `set_compiler_option()` parameter for package and source names uses dotted names, such as `abc.def` as a package name, even though schema object names use slash syntax, such as `abc/def` as a package name.

- When you specify a package name, be aware that the option will apply to any included packages as well. A setting of `a.b.MyPackage` sets the option for any source schema objects whose names are of the following form:

      a/b/MyPackage/subpackage/...

- Specifying `''` (empty set of single-quotes) as a package name makes the option apply to the root and all subpackages, effectively making it apply to all packages in your schema.

### Naming of Loaded Source and Generated Class and Resource Schema Objects

When you use the server-side SQLJ translator, such as when you use `loadjava` on a `.sqlj` file with the `-resolve` option enabled, the output generated by the server-side translator is essentially identical to what would be generated on a client. This output consists of a compiled class for each class you defined in the source and a compiled class for each iterator and connection context class.

As a result, the following schema objects will be produced when you load a `.sqlj` file into the server with `loadjava` and have it translated and compiled:

- A source schema object for the original source code

- A class schema object for each class you defined in the source

- A class schema object for each iterator or connection context class you declared in the source

  But presumably you will not need to declare connection context classes in code that will run in the server, unless it is to specify type maps for user-defined types.

The full names of these schema objects are determined as described in the following subsections. Use the `loadjava` `-verbose` option for a report of schema objects produced and what they are named.

Full Name of Source

When you load a source file into the server, regardless of whether it is translated and compiled, a source schema object is produced. The full name of this schema object is determined by the package and class names in the source code. Any path information you supply to `loadjava` on the command line is irrelevant to the determination of the name of the schema object.

For example, if `Foo.sqlj` defines the `Foo` class in the `x.y` package and defines or declares no other classes, then the full name of the resulting source schema object is:

    x/y/Foo

Note that the `.sqlj` extension is dropped.

If you define additional classes or declare iterator or connection context classes, then the source schema object is named according to the first public class definition or declaration encountered, or, if there are no public classes, the first class definition. In the server, there can be more than one public class definition in a single source.

For example, if `Foo.sqlj` is still in the `x.y` package, defines public class `Bar` first and then class `Foo`, and has no public iterator or connection context class declarations preceding the definition of `Bar`, then the full name of the resulting source schema object is:

    x/y/Bar

However, if the declaration of public iterator class `MyIter` precedes the `Bar` and `Foo` class definitions, then the full name of the resulting source schema object is:

    x/y/MyIter

Full Names of Generated Classes

Class schema objects are generated for each class you defined in the source, each iterator you declared, and the profile-keys class. The naming of the class schema objects is based on the class names and the package name from the source code.

This discussion continues the example in "Full Name of Source". Presume your source code specifies the `x.y` package, defines public class `Bar` then class `Foo`, then declares public iterator class `MyIter`. The full names of the class schema objects for the classes you define and declare are as follows:

    x/y/Bar
    x/y/Foo
    x/y/MyIter

Note that `.class` is not appended.

Note: It is recommended that the source name always match the first public class defined or, if there are no public classes, the first class defined. This will avoid possible differences between client-side and server-side behavior.

The name of the original source file, as well as any path information you specify when loading the source into the server, is irrelevant in determining the names of the generated classes. If you define inner classes or anonymous classes in your code, then they are named according to the conventions of the standard `javac` compiler.

### Error Output from the Server Embedded Translator

SQLJ error processing in the server is similar to general Java error processing in the server. SQLJ errors are directed into the `USER_ERRORS` table of the user schema. You can `SELECT` from the `TEXT` column of this table to get the text of a given error message.

However, if you use `loadjava` to load your SQLJ source, then `loadjava` also captures and writes the error messages from the server-side translator.

Informational messages and suppressible warnings are withheld by the server-side translator in a way that is equivalent to the operation of the client-side translator with a `-warn=noportable,noverbose` setting, which is the default.

See Also: "Translator Warnings (-warn)"

### Publishing the Application After Loading Source Files

Before using your SQLJ code in the server, you must publish the top-level methods, as is true of any Java code you use in the server. Publishing includes writing call descriptors, mapping data types, and setting parameter modes. For information, refer to Oracle Database Java Developer's Guide.

## Dropping Java Schema Objects

To complement the `loadjava` utility, Oracle provides the `dropjava` utility to remove Java source, class, and resource schema objects. It is recommended that any schema object loaded into the server using `loadjava` be removed using `dropjava` only.

The `dropjava` utility transforms command-line file names and JAR file contents to schema object names, then removes the schema objects. You can enter `.sqlj`, `.java`, `.class`, `.ser`, and `.jar` files on the command line in any order.

You should always remove Java schema objects in the same way that you first loaded them. If you load a `.sqlj` source file and translate it in the server, then run `dropjava` on the same source file. If you translate on a client and load classes and resources directly, then run `dropjava` on the same classes and resources.

For example, if you run `loadjava` on `Foo.sqlj`, then execute `dropjava` on the same file name, as follows:

    % dropjava -user scott/tiger Foo.sqlj

If you translate your program on the client and load it using JAR file containing the generated components, then use the same JAR file name to remove the program:

    % dropjava -user scott/tiger Foo.jar

If you translate your program on the client and load the generated components using the `loadjava` command line, then remove them using the `dropjava` command line, as follows (assume `-codegen=oracle` and no iterator classes):

    % dropjava -user scott/tiger Foo*.class

See Also: Oracle Database Java Developer's Guide

## Additional Server-Side Considerations

This section discusses Java multithreading in the server and recursive SQLJ calls in the server. It covers the following topics:

- Java Multithreading in the Server

- Recursive SQLJ Calls in the Server

- Verifying that Code is Running in the Server

### Java Multithreading in the Server

Programs that use Java multithreading can execute in Oracle Database 10g without modification. However, while client-side programs use multithreading to improve throughput for users, there are no such benefits when Java-multithreaded code runs in the server. If you are considering porting a multithreaded application into the server, be aware of the following important differences in the functionality of multithreading in the Oracle JVM, as opposed to in client-side JVMs:

- Threads in the server run sequentially, not simultaneously.

- In the server, threads within a call die at the end of the call.

- Threads in the server are not preemptively scheduled. If one thread goes into an infinite loop, then no other threads can run.

Do not confuse Java multithreading in Oracle Database 10g with general Oracle server multithreading. The latter refers to simultaneous Oracle sessions, not Java multithreading. In the server, scalability and throughput are gained by having many individual users, each with his own session, executing simultaneously. The scheduling of Java execution for maximum throughput, such as for each call within a session, is performed by Oracle Database 10g, and not by Java.

See Also: "Multithreading in SQLJ"

### Recursive SQLJ Calls in the Server

SQLJ generally does not allow multiple SQLJ statements to use the same execution context instance simultaneously. Specifically, a statement trying to use an execution context instance that is already in use will be blocked until the first statement completes.

However, this functionality would be less desirable in the server than on a client. This is because different stored procedures or functions, which all typically use the default execution context instance, can inadvertently try to use this same execution context instance simultaneously in recursive situations. For example, one stored procedure might use a SQLJ statement to call another stored procedure that uses SQLJ statements. When these stored procedures are first created, there is probably no way of knowing when such situations might arise, so it is doubtful that particular execution context instances are specified for any of the SQLJ statements.

To address this situation, SQLJ does allow multiple SQLJ statements to use the same execution context instance simultaneously if this results from recursive calls.

Consider an example of a recursive situation to see what happens to status information in the execution context instance. Presume that all statements use the default connection context instance and its default execution context instance. If stored procedure `proc1` has a SQLJ statement that calls stored procedure `proc2`, which also has SQLJ statements, then the statements in `proc2` will each be using the execution context instance while the procedure call in `proc1` is also using it.

Each SQLJ statement in `proc2` results in status information for that statement being written to the execution context instance, with the opportunity to retrieve that information after completion of each statement, as desired. The status information from the statement in `proc1` that calls `proc2` is written to the execution context instance only after `proc2` has finished executing, program flow has returned to `proc1`, and the operation in `proc1` that called `proc2` has completed.

To avoid confusion about execution context status information in recursive situations, execution context methods are carefully defined to update status information about a SQL operation only after the operation has completed.

Notes: To avoid confusion, use distinct execution context instances as appropriate whenever you plan to use execution context status or control methods in code that will run in the server. Be aware that if the preceding example does not use distinct execution context instances and `proc2` has any method calls to the execution context instance to change control parameters, then this will affect operations subsequently executed in `proc1`. Update batching is not supported across recursive calls. By default, only the top-level procedure will perform batching, if enabled. This limitation can be avoided by using explicit execution context instances.

See Also: "Execution Context Methods"

### Verifying that Code is Running in the Server

A convenient way to verify that your code is actually running in the server is to use the static `getProperty()` method of the `java.lang.System` class to retrieve the `oracle.server.version` Java property. If this property contains a version number, then you are running in the server. If it is `null`, then you are not. Here is an example:

    ...
    if (System.getProperty("oracle.server.version") != null
    {
       // (running in server)
    }
    ...

Note: Do not use the `getProperties()` method, as this causes a security exception in the server.